﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Biz_Dal.Biz;
using System.Data.Linq.SqlClient;

namespace Biz_Dal.Dal
{
    public class DalSach
    {
       
        internal static int CountListSach()
        {
            DalCommon dal = new DalCommon();
            var query = (from vt in dal.BookIdeas_ShopDataContext.Saches select vt).Distinct().Count();
            return query;
        }

        //lấy id mới
        internal static int IdMaxSach()
        {
            DalCommon dal = new DalCommon();
            int intIdt = dal.BookIdeas_ShopDataContext.Saches.Max(u => u.idSach);
            return intIdt;
        }

        //Lấy danh sách tất cả các sách
        internal static List<BizSach> getListSachAll()
        {

            DalCommon dal = new DalCommon();
            var query = (from vt in dal.BookIdeas_ShopDataContext.Saches
                         orderby vt.idSach descending
                         select new BizSach
                         {
                            idSach=vt.idSach,
                            maSach = vt.maSach,
                            tenSach = vt.tenSach,
                            tacGia = vt.tacGia,
                            hinhSach = vt.hinhSach,
                            giaSach = vt.giaSach,
                            ndSach = vt.ndSach,
                            idNXB=vt.idNXB,
                            idTheLoai=vt.idTheLoai,
                            nxb = new BizNXB(vt.NhaXuatBan.idNXB, vt.NhaXuatBan.maNXB, vt.NhaXuatBan.tenNXB),
                            theLoai = new BizTheLoai(vt.TheLoai.idTheLoai, vt.TheLoai.maTheLoai, vt.TheLoai.tenTheLoai),
                         }).Distinct<BizSach>();
            return query.ToList<BizSach>();
        }


        //Chi tiết sách
        internal static BizSach getSachByMaSach(string maSach)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where vt.maSach == maSach
                          select new BizSach
                          {
                              idSach = vt.idSach,
                              maSach = vt.maSach,
                              tenSach = vt.tenSach,
                              tacGia = vt.tacGia,
                              hinhSach = vt.hinhSach,
                              giaSach = vt.giaSach,
                              ndSach = vt.ndSach,
                              idNXB = vt.idNXB,
                              idTheLoai = vt.idTheLoai,
                              nxb = new BizNXB(vt.NhaXuatBan.idNXB,vt.NhaXuatBan.maNXB,vt.NhaXuatBan.tenNXB),
                              theLoai = new BizTheLoai(vt.TheLoai.idTheLoai,vt.TheLoai.maTheLoai,vt.TheLoai.tenTheLoai),
                          }).Distinct<BizSach>();
            if (querry.Count() == 0)
            {
                return null;
            }
            else
            {
                return querry.Single<BizSach>();
            }
        }

        //Chi tiết sách
        internal static BizSach getSachById(int idSach)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where vt.idSach == idSach
                          select new BizSach
                          {
                              idSach = vt.idSach,
                              maSach = vt.maSach,
                              tenSach = vt.tenSach,
                              tacGia = vt.tacGia,
                              hinhSach = vt.hinhSach,
                              giaSach = vt.giaSach,
                              ndSach = vt.ndSach,
                              idNXB = vt.idNXB,
                              idTheLoai = vt.idTheLoai,
                              nxb = new BizNXB(vt.NhaXuatBan.idNXB, vt.NhaXuatBan.maNXB, vt.NhaXuatBan.tenNXB),
                              theLoai = new BizTheLoai(vt.TheLoai.idTheLoai, vt.TheLoai.maTheLoai, vt.TheLoai.tenTheLoai),
                          }).Distinct<BizSach>();
            if (querry.Count() == 0)
            {
                return null;
            }
            else
            {
                return querry.Single<BizSach>();
            }
        }

        //Lấy danh sách tất cả các sách có tên được nhập vào
        internal static List<BizSach> getListSachByName(string name)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where vt.tenSach == name
                          select new BizSach
                          {
                              idSach = vt.idSach,
                              maSach = vt.maSach,
                              tenSach = vt.tenSach,
                              tacGia = vt.tacGia,
                              hinhSach = vt.hinhSach,
                              giaSach = vt.giaSach,
                              ndSach = vt.ndSach,
                              idNXB = vt.idNXB,
                              idTheLoai = vt.idTheLoai,
                              nxb = new BizNXB(vt.NhaXuatBan.idNXB, vt.NhaXuatBan.maNXB, vt.NhaXuatBan.tenNXB),
                              theLoai = new BizTheLoai(vt.TheLoai.idTheLoai, vt.TheLoai.maTheLoai, vt.TheLoai.tenTheLoai),
                          }).Distinct<BizSach>();
            if (querry.Count() == 0)
            {
                return null;
            }
            else
            {
                return querry.ToList<BizSach>();
            }
        }

        //Lấy danh sách tất cả các sách có mã chủ đề
        internal static List<BizSach> getListSachByidTheLoai(int idTheLoai)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where vt.idTheLoai == idTheLoai
                          select new BizSach
                          {
                              idSach = vt.idSach,
                              maSach = vt.maSach,
                              tenSach = vt.tenSach,
                              tacGia = vt.tacGia,
                              hinhSach = vt.hinhSach,
                              giaSach = vt.giaSach,
                              ndSach = vt.ndSach,
                              idNXB = vt.idNXB,
                              idTheLoai = vt.idTheLoai,
                              nxb = new BizNXB(vt.NhaXuatBan.idNXB, vt.NhaXuatBan.maNXB, vt.NhaXuatBan.tenNXB),
                              theLoai = new BizTheLoai(vt.TheLoai.idTheLoai, vt.TheLoai.maTheLoai, vt.TheLoai.tenTheLoai),
                          }).Distinct<BizSach>();
            if (querry.Count() == 0)
            {
                return null;
            }
            else
            {
                return querry.ToList<BizSach>();
            }
        }

        //Tìm kiếm nâng cao
        internal static List<BizSach> getListSachByMulti(string tenSach, int idTheLoai, int idNXB, double price_start, double price_end)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          select new BizSach
                           {
                               idSach = vt.idSach,
                               maSach = vt.maSach,
                               tenSach = vt.tenSach,
                               tacGia = vt.tacGia,
                               hinhSach = vt.hinhSach,
                               giaSach = vt.giaSach,
                               ndSach = vt.ndSach,
                               idNXB = vt.idNXB,
                               idTheLoai = vt.idTheLoai,
                               nxb = new BizNXB(vt.NhaXuatBan.idNXB, vt.NhaXuatBan.maNXB, vt.NhaXuatBan.tenNXB),
                               theLoai = new BizTheLoai(vt.TheLoai.idTheLoai, vt.TheLoai.maTheLoai, vt.TheLoai.tenTheLoai),
                           }).Distinct<BizSach>();


            if(tenSach.Trim()!="")
                querry = querry.Where(vt => SqlMethods.Like(vt.tenSach, "%" + tenSach + "%"));

            if(price_start!=-1)
                querry = querry.Where(vt => vt.giaSach >= Convert.ToDouble(price_start));

            if(price_end!=-1)
                querry = querry.Where(vt => vt.giaSach <= Convert.ToDouble(price_end));
            
            if(idTheLoai!=-1)
                querry = querry.Where(vt => vt.idTheLoai==idTheLoai);

            if(idNXB!=-1)
                querry = querry.Where(vt => vt.idNXB == idNXB);

            if (querry.Count() == 0)
            {
                return null;
            }
            else
            {
                return querry.ToList<BizSach>();
            }
        }

        //Danh sách gần đúng các sách có mã sách được nhập vào
        internal static List<BizSach> getLikeSachByMa(string maSach)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where SqlMethods.Like(vt.maSach, "%" + maSach + "%")
                          select new BizSach
                          {
                              idSach = vt.idSach,
                              maSach = vt.maSach,
                              tenSach = vt.tenSach,
                              tacGia = vt.tacGia,
                              hinhSach = vt.hinhSach,
                              giaSach = vt.giaSach,
                              ndSach = vt.ndSach,
                              idNXB = vt.idNXB,
                              idTheLoai = vt.idTheLoai,
                              nxb = new BizNXB(vt.NhaXuatBan.idNXB, vt.NhaXuatBan.maNXB, vt.NhaXuatBan.tenNXB),
                              theLoai = new BizTheLoai(vt.TheLoai.idTheLoai, vt.TheLoai.maTheLoai, vt.TheLoai.tenTheLoai),
                          }).Distinct<BizSach>();
            if (querry.Count() == 0)
            {
                return null;
            }
            else
            {
                return querry.ToList<BizSach>();
            }
        }

        //Lấy danh sách gần đúng tất cả các sách có tên được nhập vào
        internal static List<BizSach> getListLikeSachByName(string name)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where SqlMethods.Like(vt.tenSach, "%" + name + "%")
                          select new BizSach
                          {
                              idSach = vt.idSach,
                              maSach = vt.maSach,
                              tenSach = vt.tenSach,
                              tacGia = vt.tacGia,
                              hinhSach = vt.hinhSach,
                              giaSach = vt.giaSach,
                              ndSach = vt.ndSach,
                              idNXB = vt.idNXB,
                              idTheLoai = vt.idTheLoai,
                              nxb = new BizNXB(vt.NhaXuatBan.idNXB, vt.NhaXuatBan.maNXB, vt.NhaXuatBan.tenNXB),
                              theLoai = new BizTheLoai(vt.TheLoai.idTheLoai, vt.TheLoai.maTheLoai, vt.TheLoai.tenTheLoai),
                          }).Distinct<BizSach>();
            if (querry.Count() == 0)
            {
                return null;
            }
            else
            {
                return querry.ToList<BizSach>();
            }
        }

        //Lấy danh sách gần đúng tất cả các sách có tác giả được nhập vào
        internal static List<BizSach> getListLikeSachByTacGia(string tgia)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where SqlMethods.Like(vt.tacGia, "%" + tgia + "%")
                          select new BizSach
                          {
                              idSach = vt.idSach,
                              maSach = vt.maSach,
                              tenSach = vt.tenSach,
                              tacGia = vt.tacGia,
                              hinhSach = vt.hinhSach,
                              giaSach = vt.giaSach,
                              ndSach = vt.ndSach,
                              idNXB = vt.idNXB,
                              idTheLoai = vt.idTheLoai,
                              nxb = new BizNXB(vt.NhaXuatBan.idNXB, vt.NhaXuatBan.maNXB, vt.NhaXuatBan.tenNXB),
                              theLoai = new BizTheLoai(vt.TheLoai.idTheLoai, vt.TheLoai.maTheLoai, vt.TheLoai.tenTheLoai),
                          }).Distinct<BizSach>();
            if (querry.Count() == 0)
            {
                return null;
            }
            else
            {
                return querry.ToList<BizSach>();
            }
        }

        //Thêm sách
        internal static void insertSach(BizSach vtDTO)
        {
            DalCommon dal = new DalCommon();
            Sach vt = new Sach();
            vt.maSach = vtDTO.maSach;
            vt.tenSach = vtDTO.tenSach;
            vt.tacGia = vtDTO.tacGia;
            vt.hinhSach = vtDTO.hinhSach;
            vt.giaSach = vtDTO.giaSach;
            vt.ndSach = vtDTO.ndSach;
            vt.idTheLoai = vtDTO.idTheLoai;
            vt.idNXB = vtDTO.idNXB;
            dal.BookIdeas_ShopDataContext.Saches.InsertOnSubmit(vt);
            dal.BookIdeas_ShopDataContext.SubmitChanges();
        }

        //Xóa sách
        internal static void deleteSach(int idSach)
        {
            DalCommon dal = new DalCommon();
            IEnumerable<Sach> list = from vt in dal.BookIdeas_ShopDataContext.Saches
                                     where vt.idSach == idSach
                                      select vt;
            foreach (Sach querry in list)
            {
                dal.BookIdeas_ShopDataContext.Saches.DeleteOnSubmit(querry);
            }
            dal.BookIdeas_ShopDataContext.SubmitChanges();
        }

        //Sửa sách
        internal static void updateSach(BizSach vtDTO)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where vt.maSach == vtDTO.maSach
                          select vt).FirstOrDefault<Sach>();
            querry.maSach = vtDTO.maSach;
            querry.tenSach = vtDTO.tenSach;
            querry.tacGia = vtDTO.tacGia;
            querry.hinhSach = vtDTO.hinhSach;
            querry.giaSach = vtDTO.giaSach;
            querry.ndSach = vtDTO.ndSach;
            querry.idTheLoai = vtDTO.idTheLoai;
            querry.idNXB = vtDTO.idNXB;
            dal.BookIdeas_ShopDataContext.SubmitChanges();
        }

        //update ảnh
        internal static void updateImage(int id,System.Data.Linq.Binary image)
        {
            DalCommon dal = new DalCommon();
            var querry = (from vt in dal.BookIdeas_ShopDataContext.Saches
                          where vt.idSach == id
                          select vt).FirstOrDefault<Sach>();

            querry.hinhSach = image;
            dal.BookIdeas_ShopDataContext.SubmitChanges();
        }

    }
}
